-- 维修入库,07
-- 修复入库,08
-- 报废,11
-- 维修出库,34
-- 维修盘点,48
-- 芯片替换损坏,49
-- 芯片替换无芯片,50

with scan_record as (
    select rfid                                                                     --rfid
         , rfid_flag                                                                --1百世循环袋2极兔循环袋3文件循环袋4承映循环袋
         , scan_type_code                                                           --扫描类型code
         , scan_type_name                                                           --扫描类型名称
         , package_code                                                             --包号
         , scan_time                                                                --扫描时间
         , input_time                                                               --录入时间
         , scan_network_code                                                        --扫描网点code
         , scan_network_name                                                        --扫描网点名称
         , scan_staff_code                                                          --扫描员工code
         , scan_staff_name                                                          --扫描员工名称
         , dest_network_code                                                        --目的地网点code
         , dest_network_name                                                        --目的地网点名称
         , scan_id                                                                  --扫描id
         , date_time                                                                --扫描日期
         , network_type                                                             --网点类型
         , lag(scan_info)
               over (partition by rfid order by scan_time )  as before_network_info --上一个网点信息
         , lead(scan_info)
                over (partition by rfid order by scan_time ) as next_network_info   --下一个网点信息
    from (
             select t1.rfid
                  , t1.scan_type_code
                  , t1.scan_type_name
                  , t1.package_code
                  , t1.scan_time
                  , t1.input_time
                  , t1.scan_network_code
                  , t1.scan_network_name
                  , t1.scan_staff_code
                  , t1.scan_staff_name
                  , t1.dest_network_code
                  , t1.dest_network_name
                  , t1.scan_id
                  , substr(t1.scan_time, 1, 10)                                                                                           as date_time
                  , case
                        when substr(rfid, 1, 1) = '8' and length(rfid) = 10 then 1
                        when substr(rfid, 1, 1) = '6' and length(rfid) = 14 then 2
                        when substr(rfid, 1, 1) = '9' and length(rfid) = 16 then 3
                        when substr(rfid, 1, 1) in ('1', '2') and length(rfid) = 16 then 4
                        else 0 end                                                                                                        as rfid_flag
                  , t2.network_type
                  , row_number()
                     over (partition by substr(t1.scan_time, 1, 10),t1.scan_network_code,t1.scan_type_code,rfid order by scan_time desc ) as rn
                  , concat(scan_network_code, '-', scan_type_code, '-', scan_type_name, '-',
                           dest_network_code,'-',dest_network_name)                                                                                             as scan_info
             from jms_dwd.dwd_auto_rb_scan_record_base_dt t1
                      left join jms_dim.dim_network_whole_massage t2
                                on t1.scan_network_code = t2.code
             where t1.dt <= '{{ execution_date | cst_ds }}'
               and t1.dt >= date_add('{{ execution_date | cst_ds }}', -6)
               and t1.scan_type_code in ('07', '08', '11', '34', '48', '49', '50')
               and substr(scan_time, 1, 10) >= date_add('{{ execution_date | cst_ds }}', -6)
               and substr(scan_time, 1, 10) <= '{{ execution_date | cst_ds }}'
         ) a
    where rn = 1
      and rfid_flag in (1, 2, 3, 4)
)
insert
overwrite
table
jms_dm.dm_dev_repair_network_monitor_rfid_detail_mid_dt
partition
(
dt
)
select t1.rfid                                                     as rfid                --rfid
     , t1.rfid_flag                                                as rfid_flag           --1百世循环袋2极兔循环袋3文件循环袋4承映循环袋
     , t1.scan_type_code                                           as scan_type_code      --扫描类型code
     , t1.scan_type_name                                           as scan_type_name      --扫描类型名称
     , t1.package_code                                             as package_code        --包号
     , t1.scan_time                                                as scan_time           --扫描时间
     , t1.input_time                                               as input_time          --录入时间
     , t1.scan_network_code                                        as scan_network_code   --扫描网点code
     , t1.scan_network_name                                        as scan_network_name   --扫描网点名称
     , t1.scan_staff_code                                          as scan_staff_code     --扫描员工code
     , t1.scan_staff_name                                          as scan_staff_name     --扫描员工名称
     , t1.dest_network_code                                        as dest_network_code   --目的地网点code
     , t1.dest_network_name                                        as dest_network_name   --目的地网点名称
     , t1.scan_id                                                  as scan_id             --扫描id
     , t1.date_time                                                as date_time           --扫描日期
     , t1.network_type                                             as network_type        --网点类型
     , if(t1.network_type = 5 and t2.agent_code = '3021888', 1, 0) as is_repair           --是否维修点1是0否
     , split(before_network_info, '-')[0]                          as before_network_code --上一个网点
     , split(before_network_info, '-')[1]                          as before_type_code    --上一个网点扫描类型code
     , split(before_network_info, '-')[2]                          as before_type_name    --上一个网点扫描类型name
     , split(before_network_info, '-')[3]                          as before_dest_code    --上一个网点目的网点code
     , split(before_network_info, '-')[4]                          as before_dest_name    --上一个网点目的网点name
     , t3.name                                                     as before_network_name --上一个网点name
     , t3.network_type                                             as before_network_type --上一个网点类型
     , if(t3.network_type = 5 and t3.agent_code = '3021888', 1, 0) as is_repair_before    --上一站是否维修点1是0否
     , split(next_network_info, '-')[0]                            as next_network_code   --下一个网点
     , split(next_network_info, '-')[1]                            as next_type_code      --下一个网点扫描类型code
     , split(next_network_info, '-')[2]                            as next_type_name      --下一个网点扫描类型name
     , split(next_network_info, '-')[3]                            as next_dest_code      --下一个网点目的网点code
     , split(next_network_info, '-')[4]                            as next_dest_name      --下一个网点目的网点name
     , t4.name                                                     as next_network_name   --下一个网点name
     , t4.network_type                                             as next_network_type   --下一个网点类型
     , if(t4.network_type = 5 and t4.agent_code = '3021888', 1, 0) as is_repair_next      --下一站是否维修点1是0否
     , case
           when t1.network_type = 5 and t2.agent_code = '3021888' then 1
           when t1.network_type = 4 then 2
           else 0
    end                                                            as type                --1代表维修点2代表中心
     , t1.date_time                                                as dt
from scan_record t1
         left join jms_dim.dim_network_whole_massage t2
                   on scan_network_code = t2.code
         left join jms_dim.dim_network_whole_massage t3
                   on split(t1.before_network_info, '-')[0] = t3.code
         left join jms_dim.dim_network_whole_massage t4
                   on split(t1.next_network_info, '-')[0] = t4.code
where t1.date_time <= '{{ execution_date | cst_ds }}'
  and t1.date_time >= date_add('{{ execution_date | cst_ds }}', -2)
    distribute by dt
    , pmod(hash(rfid), 10)
;
